const connection = require('../../sql');

class DictService {
	//***字典类型 */
	// 检查 type_code 是否存在
	async checkTypeCodeExists(type_code) {
		const statement = `
      SELECT id FROM sys_dict_type
      WHERE type_code = ? AND del_flag = 'F'
    `;
		const [rows] = await connection.execute(statement, [type_code]);
		return rows.length > 0; // 返回 true 表示已存在
	}

	// 创建字典类型
	async createDictType(params) {
		const { typeName, typeCode, remark, create_id, type } = params;
		const statement = `
      INSERT INTO sys_dict_type (type_name, type_code, remark, create_id, create_time, type)
      VALUES (?, ?, ?, ?, ?, ?)
    `;
		const [result] = await connection.execute(statement, [
			typeName,
			typeCode,
			remark || '',
			create_id,
			new Date(),
			type, // 新增 type 字段
		]);
		return result;
	}

	// 获取字典类型列表
	async getDictTypeList({ typeName, typeCode, page, limit }) {
		const offset = (page - 1) * limit; // 保持为数字

		// 动态 SQL 条件
		const conditions = ['del_flag = "F"'];
		const params = [];

		if (typeName) {
			conditions.push('type_name LIKE COALESCE(?, type_name)');
			params.push(`%${typeName}%`);
		} else {
			conditions.push('type_name = COALESCE(?, type_name)');
			params.push(null);
		}

		if (typeCode) {
			conditions.push('type_code LIKE COALESCE(?, type_code)');
			params.push(`%${typeCode}%`);
		} else {
			conditions.push('type_code = COALESCE(?, type_code)');
			params.push(null);
		}

		const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

		// 查询总记录数
		const countStatement = `
          SELECT COUNT(*) as total 
          FROM sys_dict_type 
          ${whereClause}
        `;
		const countParams = params.slice(0);
		const [countRows] = await connection.execute(countStatement, countParams);
		const total = countRows[0].total;

		// 查询分页数据
		const statement = `
          SELECT
            id,
            type_name AS typeName,
            type_code AS typeCode,
            is_lock AS isLock,
            is_show AS isShow,
            del_flag AS delFlag,
            remark,
            create_time AS createTime,
            update_time AS updateTime,
            delete_time AS deleteTime,
            create_id AS createId,
            update_id AS updateId,
            delete_id AS deleteId,
            type 
          FROM sys_dict_type
          ${whereClause}
          ORDER BY create_time ASC
          LIMIT ? OFFSET ?
        `;
		const queryParams = [...params, String(limit), String(offset)]; // 确保为数字
		const [rows] = await connection.execute(statement, queryParams);

		return {
			list: rows,
			pagination: {
				total,
				page,
				limit,
			},
		};
	}

	// 更新字典类型
	async updateDictType(id, params) {
		const { typeName, typeCode, remark, update_id } = params;
		const statement = `
      UPDATE sys_dict_type
      SET type_name = ?, type_code = ?, remark = ?, update_id = ?, update_time = ?
      WHERE id = ? AND del_flag = 'F'
    `;
		const [result] = await connection.execute(statement, [typeName, typeCode, remark || '', update_id, new Date(), id]);
		if (result.affectedRows === 0) throw new Error('字典类型不存在或已被删除');
		return result;
	}

	// 删除字典类型（支持批量）
	async deleteDictType(ids, userId) {
		const placeholders = ids.map(() => '?').join(',');
		const statement = `
          UPDATE sys_dict_type
          SET del_flag = 'T', delete_id = ?, delete_time = ?
          WHERE id IN (${placeholders}) AND del_flag = 'F'
        `;
		const params = [userId, new Date(), ...ids];
		const [result] = await connection.execute(statement, params);
		if (result.affectedRows === 0) {
			throw new Error('没有找到可删除的字典类型');
		}
		return result;
	}

	//***字典值 */

	// 检查 code_name 是否在同一类型下重复
	async checkCodeNameExists(sysDictTypeId, codeName) {
		const statement = `
          SELECT id 
          FROM sys_dict 
          WHERE sys_dict_type_id = ? AND code_name = ? AND del_flag = 'F'
        `;
		const [rows] = await connection.execute(statement, [sysDictTypeId, codeName]);
		return rows.length > 0; // 返回 true 表示已存在
	}

	// 创建字典值
	async createDict(params) {
		const { sysDictTypeId, codeName, alias, sort, remark, create_id, callbackShowStyle } = params;

		// 检查 code_name 是否重复
		const exists = await this.checkCodeNameExists(sysDictTypeId, codeName);
		if (exists) {
			throw new Error('字典中已存在该名称');
		}

		// 查询当前类型下最大 ID
		const maxIdStatement = `
          SELECT MAX(id) as maxId 
          FROM sys_dict 
          WHERE sys_dict_type_id = ?
        `;
		const [maxIdRows] = await connection.execute(maxIdStatement, [sysDictTypeId]);
		const maxId = maxIdRows[0].maxId || 0;

		// 计算新 ID
		const baseId = Number(sysDictTypeId) * 1000;
		const sequence = maxId ? Number(String(maxId).slice(-3)) + 1 : 1;
		const newId = baseId + sequence;

		// 插入记录
		const statement = `
          INSERT INTO sys_dict (id, sys_dict_type_id, code_name, alias, sort, remark, create_id, create_time, callback_show_style)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;
		const [result] = await connection.execute(statement, [
			newId,
			sysDictTypeId,
			codeName,
			alias || '',
			sort || 0,
			remark || '',
			create_id,
			new Date(),
			callbackShowStyle || '',
		]);

		return result;
	}

	// 获取字典值列表
	async getDictList({ sysDictTypeId, codeName, page, limit }) {
		const offset = (page - 1) * limit;
		const conditions = ['del_flag = "F"'];
		const params = [];

		if (sysDictTypeId) {
			conditions.push('sys_dict_type_id = COALESCE(?, sys_dict_type_id)');
			params.push(sysDictTypeId);
		} else {
			conditions.push('sys_dict_type_id = COALESCE(?, sys_dict_type_id)');
			params.push(null);
		}

		if (codeName) {
			conditions.push('code_name LIKE COALESCE(?, code_name)');
			params.push(`%${codeName}%`);
		} else {
			conditions.push('code_name = COALESCE(?, code_name)');
			params.push(null);
		}

		const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

		const countStatement = `
      SELECT COUNT(*) as total 
      FROM sys_dict 
      ${whereClause}
    `;
		const countParams = params.slice(0);
		const [countRows] = await connection.execute(countStatement, countParams);
		const total = countRows[0].total;

		const statement = `
      SELECT
        id,
        sys_dict_type_id AS sysDictTypeId,
        code_name AS codeName,
        alias,
        sort,
        remark,
        is_lock AS isLock,
        is_show AS isShow,
        del_flag AS delFlag,
        create_time AS createTime,
        update_time AS updateTime,
        delete_time AS deleteTime,
        create_id AS createId,
        update_id AS updateId,
        delete_id AS deleteId,
		callback_show_style AS callbackShowStyle
      FROM sys_dict
      ${whereClause}
      ORDER BY sort ASC, create_time DESC
      LIMIT ? OFFSET ?
    `;
		const queryParams = [...params, String(limit), String(offset)];
		const [rows] = await connection.execute(statement, queryParams);

		return {
			list: rows,
			pagination: {
				total,
				page,
				limit,
			},
		};
	}

	// 更新字典值
	async updateDict(id, params) {
		const { codeName, alias, sort, remark, update_id, callbackShowStyle } = params;
		const statement = `
      UPDATE sys_dict
      SET code_name = ?, alias = ?, sort = ?, remark = ?, update_id = ?, update_time = ?, callback_show_style = ? WHERE id = ? AND del_flag = 'F'
    `;
		const [result] = await connection.execute(statement, [
			codeName,
			alias || '',
			sort || 0,
			remark || '',
			update_id,
			new Date(),
			callbackShowStyle,
			id,
		]);
		if (result.affectedRows === 0) throw new Error('字典值不存在或已被删除');
		return result;
	}

	// 删除字典值（支持批量）
	async deleteDict(ids, userId) {
		const placeholders = ids.map(() => '?').join(',');
		const statement = `
      UPDATE sys_dict
      SET del_flag = 'T', delete_id = ?, delete_time = ?
      WHERE id IN (${placeholders}) AND del_flag = 'F'
    `;
		const params = [userId, new Date(), ...ids];
		const [result] = await connection.execute(statement, params);
		if (result.affectedRows === 0) {
			throw new Error('没有找到可删除的字典值');
		}
		return result;
	}

	// 获取所有字典数据
	async getAllDicts() {
		// 1. 查询静态字典（sys_dict 和 sys_dict_type 联表）
		const staticStatement = `
		  SELECT 
			d.id,
			d.sys_dict_type_id AS sysDictTypeId,
			d.code_name AS codeName,
			d.alias,
			d.sort,
			d.callback_show_style AS callbackShowStyle,
			d.is_lock AS isLock,
			d.is_show AS isShow,
			dt.type_name AS sysDictTypeName,
			dt.type_code AS sysDictTypeCode,
			FALSE AS isDynamic
		  FROM sys_dict d
		  INNER JOIN sys_dict_type dt ON d.sys_dict_type_id = dt.id
		  WHERE d.del_flag = 'F' AND dt.del_flag = 'F'
		  ORDER BY dt.type_code, d.sort
		`;
		let staticResults;
		try {
			[staticResults] = await connection.execute(staticStatement);
		} catch (error) {
			throw new Error(`查询静态字典失败: ${error.message}`);
		}

		// 2. 查询动态字典（从 sys_user 表生成 dynamic_user_options）
		const dynamicStatement = `
		  SELECT 
			id,
			'' AS sysDictTypeId,
			username AS codeName,
			username AS alias,
			id AS sort,
			'primary' AS callbackShowStyle,
			'F' AS isLock,
			'T' AS isShow,
			'用户信息' AS sysDictTypeName,
			'dynamic_user_options' AS sysDictTypeCode,
			TRUE AS isDynamic
		  FROM sys_user
		  WHERE del_flag = 'F'
		  ORDER BY id
		`;
		let dynamicResults;
		try {
			[dynamicResults] = await connection.execute(dynamicStatement);
		} catch (error) {
			throw new Error(`查询动态字典失败: ${error.message}`);
		}

		// 3. 合并静态和动态字典
		const allDicts = [...staticResults, ...dynamicResults];

		// 4. 按 sysDictTypeCode 分组
		const groupedDicts = {};
		allDicts.forEach((dict) => {
			const typeCode = dict.sysDictTypeCode;
			if (!groupedDicts[typeCode]) {
				groupedDicts[typeCode] = [];
			}
			groupedDicts[typeCode].push({
				id: dict.id,
				sysDictTypeId: dict.sysDictTypeId,
				codeName: dict.codeName,
				alias: dict.alias,
				sort: dict.sort,
				callbackShowStyle: dict.callbackShowStyle,
				isLock: dict.isLock,
				isShow: dict.isShow,
				sysDictTypeName: dict.sysDictTypeName,
				sysDictTypeCode: dict.sysDictTypeCode,
				isDynamic: dict.isDynamic,
			});
		});

		return groupedDicts;
	}
}

module.exports = new DictService();
